package com.gljx.web.controller;

import com.gljx.util.*;
import com.gljx.web.Entity.*;
import com.gljx.web.service.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

@Controller
@RequestMapping("/")
public class ExamOrderController {

	@Autowired
	private ExamOrderService examOrderService;
	
	@Autowired
	private DepartmentService departmentService;
	
	@Autowired
	private ClazzService clazzService;
	
	@Autowired
	private StudentController studentController;
	
	@Autowired
	private CustomLogService customLogService;
	
	private ImportExecl poi = new ImportExecl();
	
	@Autowired
	private StudentService studentService;
	
	
	/**
	 * 查询考试预约
	 * @param request
	 * @return
	 * @createTime 2017-11-29 11:06 am
	 * @author zyq.
	 * @throws Exception
	 */
	@RequestMapping(value="/selectExamOrder/look.action")
	public String selectExamOrder(HttpServletRequest request) throws Exception {
		HttpSession session=request.getSession();
		User user=(User) session.getAttribute("user");                                   //获取已经登录用户数据
		Department department=departmentService.queryAdminByFid(user.getFcompanyid());   //获取登录用户组织ID
		Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
		         ca.setTime(new Date()); // 设置时间为当前时间
		         ca.add(Calendar.DATE, -1);// 月份减1
		Date resultDate = ca.getTime(); // 结果
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String beginTime = sdf.format(resultDate);
		String endTime = sdf.format(new Date());
		PageBean<ExamOrder> pBean = getPageBean(request, department.getFid(), beginTime, endTime);
		//生成信息总条数
		String messageAmount="共有"+pBean.getTr()+"条信息";
		request.setAttribute("messageAmount",messageAmount);
		request.setAttribute("beginTime", beginTime);
		request.setAttribute("endTime", endTime);
		request.setAttribute("examOrders", pBean.getBeanList());
		request.setAttribute("pb", pBean);
		request.setAttribute("company", department);
		return "backHome/orderManage/examOrder/examOrderList";
	}
	
	
	/**
	 * 进入新增页面
	 * @createTime 2017-11-29 11:46 am
	 * @author zyq.
	 * @param request
	 * @return
	 * @throws Exception 
	 */
	@RequestMapping(value="/addExamOrder")
	public String addExamOrder(HttpServletRequest request) throws Exception {
		HttpSession session=request.getSession();
		Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
		         ca.setTime(new Date()); // 设置时间为当前时间
		         ca.add(Calendar.DATE, -1);// 月份减1
	    Date resultDate = ca.getTime(); // 结果
	    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	    String beginTime = sdf.format(resultDate);
	    String endTime = sdf.format(new Date());
		User user=(User) session.getAttribute("user");                                  		//获取已经登录用户数据
		int  state = 1;
		List<Clazz> clazzs = clazzService.selectByCompanyID(user.getFcompanyid());
		Department department=departmentService.queryAdminByFid(user.getFcompanyid());  	   //获取登录用户组织ID
        Map map = studentController.getPageBeanAsEnrol(request, null, null, "0", beginTime, endTime, "yy", state, "", ""); //调用学员方法
        request.setAttribute("department", department);
		request.setAttribute("clazzs", clazzs);
		request.setAttribute("flag", 1);				//是否为新增记录	
		request.setAttribute("beginTime", beginTime);
        request.setAttribute("endTime", endTime);
		request.setAttribute("pb", map.get("pb"));
        request.setAttribute("students", map.get("sList"));
		return "backHome/orderManage/examOrder/detailExamOrder";
	}
	
	/**
	 * 保存考试预约
	 * @createTime 2017-11-30 11:46 am
	 * @author zyq.
	 * @param request
	 * @param examOrder
	 * @return
	 */
	@RequestMapping(value="/saveExamOrder")
	public String saveExamOrder(HttpServletRequest request,ExamOrder examOrder,String state) {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		//利用UUID生成fid
		examOrder.setFid(GetFid.UUIDIntoFid());
		//控制单元id
		examOrder.setFcontrolunitid(user.getFcompanyid());
		//创建人id
		examOrder.setFcreatorid(user.getFid());
		//公司id
		examOrder.setCfficompanyid(user.getFcompanyid());
		//获取当前时间
		String createTime = TimeFormat.timeFormat(new Date());
		examOrder.setFcreatetime(createTime);
        /*String fnumber = customLogService.selectFunmber("CT_DS_EXAMSUBSCRIBE",user.getFcompanyid());*/
        examOrder.setFnumber(GetFnumber.fnumbers("KY"));
		examOrder.setFhandlerid(user.getFid());
		examOrderService.insertExamOrder(examOrder);
		if("1".equals(state))
			state = "科目一";
		else if("2".equals(state))
			state = "科目二";
		else state = "科目三";
		customLogService.addLog("考试预约", "办理了学员:"+examOrder.getCfstudentno()+examOrder.getCfstudentname()+","+ state+"考试预约,单据编号:"+examOrder.getFnumber(), examOrder.getCfstudentid(), user.getFid());
		request.setAttribute("type",2);
		return "forward:addExamOrder";
	}
	
	
	/**
	 * 修改考试预约
	 * @param request
	 * @param fid
	 * @createTime 2017-12-04 11:46 am
	 * @author zyq.
	 * @return
	 * @throws Exception 
	 */
	@RequestMapping(value="/selectExamOrderByFid")
	public String selectExamOrderByFid(HttpServletRequest request,String fid) throws Exception {
		fid = fid.replace(" ", "+");
		User user = (User) request.getSession().getAttribute("user");
		ExamOrder examOrder = examOrderService.selectExamOrderByFid(fid);
		Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
				 ca.setTime(new Date()); // 设置时间为当前时间
				 ca.add(Calendar.DATE, -1);// 月份减1
		Date resultDate = ca.getTime(); // 结果
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String beginTime = sdf.format(resultDate);
		String endTime = sdf.format(new Date());
		List<Clazz> clazzs = clazzService.selectByCompanyID(user.getFcompanyid());
		Map map = studentController.getPageBeanAsEnrol(request, "", "", "0", beginTime, endTime,"yy",Integer.valueOf(examOrder.getCfbilltype())-1,"",""); //调用学员方法
		request.setAttribute("beginTime", beginTime);
        request.setAttribute("endTime", endTime);
		request.setAttribute("pb", map.get("pb"));
        request.setAttribute("students", map.get("sList"));
		request.setAttribute("flag", 2);
        request.getSession().setAttribute("fid", examOrder.getCfstudentid());
		request.setAttribute("clazzs", clazzs);
		request.setAttribute("examOrder", examOrder);
		return "backHome/orderManage/examOrder/detailExamOrder";
	}
	
	/**
	 * 更新考试预约记录
	 * @param request
	 * @param examOrder
	 * @createTime 2017-12-04 1:46 pm
	 * @author zyq.
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="/updateExamOrder")
	public String updateExamOrder(HttpServletRequest request,ExamOrder examOrder,String fnumbers) throws Exception {
		HttpSession session = request.getSession();
		User user = (User) session.getAttribute("user");     
		examOrder.setFlastupdatetime(TimeFormat.timeFormat(new Date()));
		examOrder.setFlastupdateuserid(user.getFid());
		examOrderService.updateExamOrder(examOrder);
		customLogService.addLog("考试预约", "修改了学员:"+examOrder.getCfstudentno()+examOrder.getCfstudentname()+"的考试预约,单据编号:"+fnumbers, examOrder.getCfstudentid(), user.getFid());
		selectExamOrder(request);
		request.setAttribute("type",2);
		return "backHome/orderManage/examOrder/examOrderList";
	}
	
	/**
	 * 根据条件查询考试预约
	 * @param request
	 * @param beginTime
	 * @param endTime
	 * @param examOrder
	 * @createTime 2017-12-04 11:46 am
	 * @author zyq.
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="/selectExamOrderByCon")
	public String selectExamOrderByCon(HttpServletRequest request,String beginTime,String endTime,ExamOrder examOrder) throws Exception {
		HttpSession session = request.getSession();
		User user = (User) session.getAttribute("user");                                   //获取已经登录用户数据
		Department department = departmentService.queryAdminByFid(user.getFcompanyid());   //获取登录用户组织ID
		PageBean<ExamOrder> pBean = getPageBean1(request, department.getFid(), beginTime, endTime, examOrder,false);
		//生成信息总条数
		String messageAmount="共有"+pBean.getTr()+"条信息";
		request.setAttribute("messageAmount",messageAmount);
		request.setAttribute("beginTime", beginTime);
		request.setAttribute("endTime", endTime);
		request.setAttribute("examOrders", pBean.getBeanList());//表信息
		request.setAttribute("pb", pBean);
		request.setAttribute("examOrder1", examOrder);//搜索条件
		return "backHome/orderManage/examOrder/examOrderList";
	}




	/**
	 * 批量删除考试预约
	 * @param request
	 * @param fid
	 * @return
	 *  @createTime 2017-12-06 11:46 am
	 * @author zyq.
	 * @throws Exception
	 */
	@RequestMapping(value="/deleteExamOrderByFid")
	public String deleteExamOrderByFid(HttpServletRequest request ,String fid) throws Exception {
		String [] fids = fid.split(",");
		User user=(User) request.getSession().getAttribute("user");
		for(int i=0 ;i<fids.length;i++){
			Student student = new Student();
			ExamOrder examOrder = examOrderService.selectExamOrderByFid(fids[i]);
			List<Student> list = studentService.selectStudentByFid(examOrder.getCfstudentid());
			if(!list.isEmpty())
				student = list.get(0);
			customLogService.addLog("考试预约", "删除了学员:"+student.getFnumber()+student.getFnameL2()+"的考试预约,单据编号"+examOrder.getFnumber(), student.getFid(), user.getFid());
		}
		examOrderService.deleteExamOrderByFid(fids);
		selectExamOrder(request);
		return "backHome/orderManage/examOrder/examOrderList";
	}




	/**
	 * 进入批量考试预约
	 * @return
	 */
	@RequestMapping(value="/selectExamOrderMass/look.action")
	public String selectExamOrderMass() {
		return "backHome/orderManage/examOrder/examOrderListMass";
	}
	
	
	/**
	 * 进入批量新增考试预约页面
	 * @param request
	 * @param examOrder
	 * @return
	 * @createTime 2017-12-06 2:46 pm
	 * @author zyq.
	 * @throws Exception 
	 */
	@RequestMapping(value="/addExamOrderMass")
	public String addExamOrderMass(HttpServletRequest request,ExamOrder examOrder) throws Exception{
		Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
		         ca.setTime(new Date()); // 设置时间为当前时间
		         ca.add(Calendar.DATE, -1);// 月份减1
		Date resultDate = ca.getTime(); // 结果
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        HttpSession session = request.getSession();
        User user = (User) session.getAttribute("user");
		String beginTime = sdf.format(resultDate);
		String endTime = sdf.format(new Date());
		int firstExam = 0;
		int notFirstExam = 0;
		Map map = new HashMap<>();
		if(examOrder.getCfbilltype().equals("2")){
            map = studentController.getPageBeanAsEnrol(request, null, null, "0", beginTime, endTime, "yy", 1, "", examOrder.getCfsqzjcxdh());
        }else if (examOrder.getCfbilltype().equals("3")) {
            map = studentController.getPageBeanAsEnrol(request, null, null, "0", beginTime, endTime, "yy", 2, "", examOrder.getCfsqzjcxdh());
        }else {
            map = studentController.getPageBeanAsEnrol(request, null, null, "0", beginTime, endTime, "yy", 3, "", examOrder.getCfsqzjcxdh());
        }
        List<ExamOrder> examOrders = examOrderService.selectExamOrderByDate(examOrder.getCfexamdate(),user.getFcompanyid(),examOrder.getCfsqzjcxdh(),examOrder.getCfbilltype(),"1");
        List<ExamOrder> examOrders2 = examOrderService.selectExamOrderByDate(examOrder.getCfexamdate(),user.getFcompanyid(),examOrder.getCfsqzjcxdh(),examOrder.getCfbilltype(),"2");
        for (ExamOrder examOrder1 : examOrders){
        	if("1".equals(examOrder1.getCfckbk()))
				++firstExam ;
        	else
				++notFirstExam ;
		}
		for (ExamOrder examOrder1 : examOrders2){
			if("1".equals(examOrder1.getCfckbk()))
				++firstExam ;
			else
				++notFirstExam ;
		}
        request.setAttribute("beginTime", beginTime);
		request.setAttribute("endTime", endTime);
		request.setAttribute("pb", map.get("pb"));
		request.setAttribute("examOrders",examOrders);
        request.setAttribute("examOrders2",examOrders2);
        request.setAttribute("students", map.get("sList"));
		request.setAttribute("examOrder", examOrder);
		request.setAttribute("success",examOrders.size());
		request.setAttribute("firstExam",firstExam);
		request.setAttribute("notFirstExam",notFirstExam);
		return "backHome/orderManage/examOrder/detailExamOrderMass";	
	}
	
	/**
	 * 保存批量考试预约
	 * @param examOrder
	 * @param request
	 * @createTime 2017-12-06 4:46 pm
	 * @author zyq.
	 * @return
	 */
	@RequestMapping(value="/saveExamOrderMass")
	public String saveExamOrderMass(ExamOrder examOrder,HttpServletRequest request,String cfsex1,String flags,
			String cfamount,String [] fids,String cfreceived,String cffavorable,String cfarrearage,String cfsubscriberesult1,
									String cfcarbyte,String [] cfremark
			){
		User user = (User) request.getSession().getAttribute("user");
		if(fids != null)
            examOrderService.deleteExamOrderByFid(fids);
		if(cfsex1.length()<2){
			String [] cfclazz = examOrder.getCfclazzid().split(",");
			String [] studentid = examOrder.getCfstudentid().split(",");
			String [] cfamounts = cfamount.split(",");
			String [] cfckbks = examOrder.getCfckbk().split(",");
			String [] cfreceiveds = cfreceived.split(",");
			String [] cffavorables = cffavorable.split(",");
			String [] cfarrearages = cfarrearage.split(",");
			String [] cfcarbytes = cfcarbyte.split(",");
			Object  cftjrs =  examOrder.getCftjr();
 			String [] cfsubscriberesults = cfsubscriberesult1.split(",");
			for(int i = 0 ; i < studentid.length ; i++){
				//利用UUID生成fid
				examOrder.setFid(GetFid.UUIDIntoFid());
				//控制单元id
				examOrder.setFcontrolunitid(user.getFcompanyid());
				//创建人id
				examOrder.setFcreatorid(user.getFid());
				//公司id
				examOrder.setCfficompanyid(user.getFcompanyid());
				//获取当前时间
				String createTime = TimeFormat.timeFormat(new Date());
				examOrder.setFcreatetime(createTime);
                /*String fnumber = customLogService.selectFunmber("CT_DS_EXAMSUBSCRIBE",user.getFcompanyid());*/
                examOrder.setFnumber(GetFnumber.fnumbers("KY"));
				if(cfsex1.equals("null")||cfsex1.equals("")){
				examOrder.setCfsex(null);
				}else{
					Long sex = new Long(cfsex1);
					examOrder.setCfsex(sex);
				}
				examOrder.setCftjr(cftjrs);
				BigDecimal arrearage =  new BigDecimal(cfarrearages[i]);
				BigDecimal amount =  new BigDecimal(cfamounts[i]);
				BigDecimal receiveds =  new BigDecimal(cfreceiveds[i]);
				BigDecimal favorables =  new BigDecimal(cffavorables[i]);
				examOrder.setCfarrearage(arrearage);
				examOrder.setCfamount(amount);
				examOrder.setCfreceived(receiveds);
				examOrder.setCffavorable(favorables);
				examOrder.setCfstudentname(examOrder.getCfstudentname());
				examOrder.setCfckbk(cfckbks[i]);
				examOrder.setCfstudentid(studentid[i]);
				examOrder.setCfclazzid(cfclazz[i]);
				examOrder.setCfremark(examOrder.getCfremark());
				examOrder.setCfsubscriberesult(cfsubscriberesults[i]);
				examOrder.setCfidcardno(examOrder.getCfidcardno());
				examOrder.setCfmobile(examOrder.getCfmobile());
				examOrder.setCfcarbyte(cfcarbytes[i]);
				examOrder.setFhandlerid(user.getFid());
				examOrderService.insertExamOrder(examOrder);
				String state;
                if("1".equals(examOrder.getCfbilltype()))
                    state = "科目一";
                else if("2".equals(examOrder.getCfbilltype()))
                    state = "科目二";
                else state = "科目三";
                customLogService.addLog("考试预约", "办理了学员:"+examOrder.getCfstudentno()+examOrder.getCfstudentname()+","+ state+"考试预约,单据编号:"+examOrder.getFnumber(), examOrder.getCfstudentid(), user.getFid());
		}}else{
		String [] cfsex = cfsex1.split(",");
		Object [] cftjrs = (Object[]) examOrder.getCftjr();
		String [] cfclazz = examOrder.getCfclazzid().split(",");
		String [] studentid = examOrder.getCfstudentid().split(",");
		String [] cfidcardno = (String[]) examOrder.getCfidcardno();
		String [] cfmobiles= (String[]) examOrder.getCfmobile();
		cfremark = (String[]) examOrder.getCfremark();                 //批量上传的数据
		String [] cfamounts = cfamount.split(",");
		String [] cfckbks = examOrder.getCfckbk().split(",");
		String [] cfreceiveds = cfreceived.split(",");
		String [] cffavorables = cffavorable.split(",");
		String [] cfarrearages = cfarrearage.split(",");
		String [] cfcarbytes = cfcarbyte.split(",");
		String [] cfstudentname = (String[]) examOrder.getCfstudentname();
		String [] cfsubscriberesults = cfsubscriberesult1.split(",");
		for(int i = 0 ; i < studentid.length ; i++){
			//利用UUID生成fid
			examOrder.setFid(GetFid.UUIDIntoFid());
			//控制单元id
			examOrder.setFcontrolunitid(user.getFcompanyid());
			//创建人id
			examOrder.setFcreatorid(user.getFid());
			//公司id
			examOrder.setCfficompanyid(user.getFcompanyid());
			//获取当前时间
			String createTime = TimeFormat.timeFormat(new Date());
			examOrder.setFcreatetime(createTime);
            /*String fnumber = customLogService.selectFunmber("CT_DS_EXAMSUBSCRIBE",user.getFcompanyid());*/
            examOrder.setFnumber(GetFnumber.fnumbers("KY"));
			if(cfsex[i].equals("null")||cfsex[i].equals("")){
				examOrder.setCfsex(null);
				}else{
					Long sex = new Long(cfsex[i]);
					examOrder.setCfsex(sex);
			}
			examOrder.setCftjr(cftjrs[i]);
			BigDecimal arrearage = "null".equals(cfarrearages[i].trim()) ? new BigDecimal(0) : new BigDecimal(cfarrearages[i]);
			BigDecimal amount =  "null".equals(cfamounts[i].trim()) ? new BigDecimal(0) : new BigDecimal(cfamounts[i]);
			BigDecimal receiveds =  "null".equals(cfreceiveds[i].trim()) ? new BigDecimal(0) : new BigDecimal(cfreceiveds[i]);
			BigDecimal favorables =  "null".equals(cffavorables[i].trim()) ? new BigDecimal(0) : new BigDecimal(cffavorables[i]);
			examOrder.setCfarrearage(arrearage);
			examOrder.setCfamount(amount);
			examOrder.setCfreceived(receiveds);
			examOrder.setCffavorable(favorables);
			examOrder.setCfstudentname(cfstudentname[i]);
			examOrder.setCfckbk(cfckbks[i]);
			examOrder.setCfstudentid(studentid[i]);
			examOrder.setCfclazzid(cfclazz[i]);
			examOrder.setCfremark(cfremark[i]);
			examOrder.setCfsubscriberesult(cfsubscriberesults[i]);
			examOrder.setCfidcardno(cfidcardno[i]);
			examOrder.setCfmobile(cfmobiles[i]);
			examOrder.setCfcarbyte(cfcarbytes[i]);
			examOrder.setFhandlerid(user.getFid());
			examOrderService.insertExamOrder(examOrder);
			customLogService.addLog("考试预约", examOrder.getCfstudentname() + "办理考试预约", examOrder.getCfstudentid(), user.getFid());
		}
		}
		request.setAttribute("type",2);
		return "forward:addExamOrderMass";
	}


	@RequestMapping(value = "/outExamOrderExcel")
	public void outExamOrderExcel(HttpServletResponse response, HttpServletRequest request,
									ExamOrder examOrder, String beginTime, String endTime) throws Exception {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		List<Object> data = new ArrayList<>();
		//查询数据
		PageBean<ExamOrder> pBean =  getPageBean1(request,user.getFcompanyid(),beginTime,endTime,examOrder,true);
		for (ExamOrder examOrder1 : pBean.getBeanList()) {
			data.add(examOrder1.getCfexamdate());//单据编号
			if("1".equals(examOrder1.getCfbilltype()))
				data.add("模拟考预约");//业务日期
			else if("2".equals(examOrder1.getCfbilltype()))
				data.add("科目一预约");
			else if("3".equals(examOrder1.getCfbilltype()))
				data.add("科目二预约");
			else if("4".equals(examOrder1.getCfbilltype()))
				data.add("科目三预约");
			else
				data.add("");
			data.add(examOrder1.getFnumber());//业务日期
			data.add(examOrder1.getCfsqzjcxdh());
			data.add(examOrder1.getCfstudentno());//制单人名称
			data.add(examOrder1.getCfstudentname());//学员
			if("1".equals(examOrder1.getCfvip()))
				data.add("是");
			else
				data.add("否");
			if("1".equals(examOrder1.getCfsubscriberesult()))
				data.add("预约成功");
			else if("2".equals(examOrder1.getCfsubscriberesult()))
				data.add("预约失败");
			else
				data.add("");
			data.add(examOrder1.getCfmobile());//学员编码
			data.add(examOrder1.getCfphone());//身份证号
			data.add(examOrder1.getCfarrearage());
			if("1".equals(examOrder1.getCfcarbyte()))
				data.add("小车");
			else if("2".equals(examOrder1.getCfcarbyte()))
				data.add("大车");
			else
				data.add("");
			data.add(examOrder1.getFdescription());
			data.add(examOrder1.getCfnexttime());
			data.add(examOrder1.getCfreceived());
			data.add(examOrder1.getCfamount());
			data.add(examOrder1.getCffavorable());
			data.add(examOrder1.getCfclazzName());
			data.add(examOrder1.getCfidcardno());
			if (examOrder1.getCfsex() == null) {
				data.add("");//性别
			} else if (examOrder1.getCfsex() == 2) {
				data.add("女");//性别
			} else {
				data.add("男");//性别
			}
			data.add(examOrder1.getCftjr());
			if("1".equals(examOrder1.getCfcarbyte()))
				data.add("初考");
			else if("2".equals(examOrder1.getCfcarbyte()))
				data.add("补考");
			else
				data.add("");
			if("1".equals(examOrder1.getCfjnbjj()))
				data.add("是");
			else
				data.add("否");
			if("1".equals(examOrder1.getCfdajj()))
				data.add("是");
			else
				data.add("否");
		}
		//头部数据
		String head[] = {"预约考试日期", "考试类型", "单据编号","申请现准驾车型", "学员编码","学员名称","VIP", "预约结果","手机","电话", "欠费金额",
				"车型", "备注", "下一次预约时间", "已收金额", "应收金额","优惠金额", "班级", "身份证号码", "性别","推荐人", "初考/补考", "技能表交接", "档案交接"};
		//长度
		Integer length[] = {4000, 3000, 7000, 2000, 6000, 3000, 1500, 2200, 4000, 2000, 2000, 2600, 4000, 4000, 3000, 2000, 3000,6000, 6000, 2000, 2000, 3000, 2000, 2000,2000};
		try {
			//导出Excel
			CommonExport.importExcel(response, "考试预约" + TimeFormat.getNowTime2() + ".xls", "考试预约", data, head, length);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}








	/**
	 * 导出EXCLE
	 * @return
	 * @createTime 2017-12-08 4:46 pm
	 * @author zyq.
	 * @throws IOException 
	 */
	@RequestMapping(value="/exportExamOrderMassExcle")
	public void  exportExamOrderMass(HttpServletResponse response) throws IOException{
		String fileName = "考试预约.xls";
        fileName = new String(fileName.getBytes("GBK"), "iso8859-1");  
        response.reset();  
        response.setHeader("Content-Disposition", "attachment;filename="  
                + fileName);// 指定下载的文件名  
        response.setContentType("application/vnd.ms-excel");  
        response.setHeader("Pragma", "no-cache");  
        response.setHeader("Cache-Control", "no-cache");  
        response.setDateHeader("Expires", 0);  
        OutputStream output = response.getOutputStream();  
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);  
        HSSFWorkbook wb = new HSSFWorkbook();  
  
        // 创建单元格样式  
        HSSFCellStyle cellStyleTitle = wb.createCellStyle();  
        // 指定单元格居中对齐  
        cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        // 指定单元格垂直居中对齐  
        cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
        // 指定当单元格内容显示不下时自动换行  
        cellStyleTitle.setWrapText(true);  
        cellStyleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyleTitle.setFillForegroundColor(HSSFColor.LAVENDER.index);
        // ------------------------------------------------------------------  
        /*HSSFCellStyle cellStyle = wb.createCellStyle();  
        // 指定单元格居中对齐  
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        // 指定单元格垂直居中对齐  
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
        // 指定当单元格内容显示不下时自动换行  
        cellStyle.setWrapText(true);  
        cellStyle.setFillForegroundColor((short)41);*/
        // ------------------------------------------------------------------  
        // 设置单元格字体  
        HSSFFont font = wb.createFont();  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
        font.setFontName("宋体");  
        font.setFontHeight((short) 200);  
        cellStyleTitle.setFont(font);  
       
  
        // 工作表名  
        String id = "姓名";  
        String name = "身份证号码";  
    
        HSSFSheet sheet = wb.createSheet();  
       /* ExcelExport exportExcel = new ExcelExport(wb, sheet);  */
        // 创建报表头部  
        /*exportExcel.createNormalHead(worksheetTitle, 2);  */
        // 定义第一行  
        HSSFRow row1 = sheet.createRow(0);  
        HSSFCell cell1 = row1.createCell(0);  
  
        //第一行第一列  
          
        cell1.setCellStyle(cellStyleTitle);  
        cell1.setCellValue(new HSSFRichTextString(id));  
        //第一行第er列  
        cell1 = row1.createCell(1);  
        cell1.setCellStyle(cellStyleTitle);  
        cell1.setCellValue(new HSSFRichTextString(name));  
  
		sheet.setColumnWidth(0,id.getBytes().length*3*256); 
		sheet.setColumnWidth(1,name.getBytes().length*3*256);
        
      /*  //定义第二行  
        HSSFRow row = sheet.createRow(2);  
        HSSFCell cell = row.createCell(1);  */
		try {  
            bufferedOutPut.flush();  
            wb.write(bufferedOutPut);  
            bufferedOutPut.close();  
        } catch (IOException e) {  
            e.printStackTrace();  
            System.out.println("Output   is   closed ");  
        } finally {  
    
        }             
	}

	/**
	 * 读取Excel
	 * @param file
	 * @param request
	 * @param response
	 * @return
	 */
	@ResponseBody
	@RequestMapping(value="/uploadExamOrderMass")
	public Map<Object, Object> uploadExamOrderMass(@RequestParam(value="file")MultipartFile file,String state,
												   HttpServletRequest request,String carType, HttpServletResponse response) {
		List<Student> students = new ArrayList<>();
		Map<Object, Object> map2 = new HashMap<>();
		String filesFileName=file.getOriginalFilename();
		List<Map<String, Object>> results = new ArrayList<>();
		try {
			uploadExcel(request,file, filesFileName);// 上传
		} catch (Exception e) {
			e.printStackTrace();
		}
		Map<String, List<StudentImport>> map = new HashMap<>();
		//System.out.println("文件名字: "+request.getServletContext().getRealPath("/")+ "xls" + File.separatorChar + filesFileName);
		//读取
		map=poi.show2(
				request.getServletContext().getRealPath("/")+ "xls" + File.separatorChar + filesFileName
				);
		if(map.size() == 0){//没有可以导入的数据！";
			return null;
		}else{
		 List<StudentImport> sList = map.get("1");
		 for(StudentImport studentImport : sList){
             List<Student> studentList = studentService.selectStudentByCfidcardnos((String) studentImport.getCfidcardno(),state,carType,GetUserSession.getUserSession(request));
			 Student dStudent;
			 if(studentList.size() >= 1){
                 dStudent = studentService.selectStudentByCfidcardnos((String) studentImport.getCfidcardno(),state,carType,GetUserSession.getUserSession(request)).get(0);
				 students.add(dStudent);
			 }else{
			 	 Map<String, Object> map3 = new HashMap<>();
				 	 map3.put("mes","身份证号码有误或车型不符合要求");
					 map3.put("name",studentImport.getStuName());
					 map3.put("idcerNo",studentImport.getCfidcardno());
				 results.add(map3);
			 }
		 }
		}
		map2.put("student",students);
		map2.put("result",results);
		return map2;
	}
	
	
	/**
	 * Excel上传
	 * @param request
	 * @throws Exception
	 */
	public  void uploadExcel(HttpServletRequest request, MultipartFile file,String fileName)throws Exception{
		String path=request.getServletContext().getRealPath("/")+"xls/";
		File fileUpload=new File(path);
		if(!fileUpload.exists()){
			fileUpload.mkdirs();
		}
		file.transferTo(new File(path+fileName));
	}

	/**
	 * 查询学员信息
	 * @return
	 */
	@RequestMapping (value = "/selectStudentByCerNoAndCompany")
	@ResponseBody
	public Student selectStudentByCerNoAndCompany(String idCerNo,HttpServletRequest request) {
		Student student = studentService.queryStudentByIdCardNo(idCerNo,GetUserSession.getUserSession(request));
		return student;
	}
	
	
	@SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类 
	 * @throws Exception
	 */
	public PageBean<ExamOrder> getPageBean(HttpServletRequest request,String fid,String beginTime,String endTime) throws Exception
	{

		int offset;//从第几条数据开始加载
		if(request.getParameter("pager.offset")==null){  
			offset=0;  
		}else{  
			offset=Integer.parseInt(request.getParameter("pager.offset"));
		}	
		String psql="select  T.FID, to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
				   + "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime," 
				   + "to_char(t.FBIZDATE,'yyyy-MM-DD')  fbizdate,"
				   + "to_char(T.CFEXAMDATE,'YYYY-MM-DD')  cfexamdate,"
				   + "to_char(T.CFNEXTTIME,'YYYY-MM-DD')  cfnexttime,"
				   + "T.FCREATORID, T.FLASTUPDATEUSERID, T.FCONTROLUNITID, "
				   + "T.FNUMBER, T.FHANDLERID, T.FDESCRIPTION, T.FHASEFFECTED, T.FAUDITORID, T.FSOURCEBILLID,  "
				   + "T.FSOURCEFUNCTION, T.FFIVOUCHERED, T.CFFICOMPANYID, T.CFSTUDENTID, T.CFSTUDENTNAME, T.CFMOBILE, "
				   + "T.CFPHONE, T.CFREMARK, T.CFSUBSCRIBERESULT, T.CFBILLTYPE,T.CFCARBYTE,  "
				   + "T.CFARREARAGE, T.CFSQZJCXDH, T.CFVIP, T.CFRECEIVED, T.CFAMOUNT, T.CFFAVORABLE, T.CFCLAZZID, T.CFIDCARDNO,"
				   + " T.CFSEX, T.CFTJR, T.CFCKBK, T.CFJNBJJ, T.CFDAJJ ,U.FNUMBER AS CFSTUDENTNO ,"  	 
				   + "	S.NAME AS FCREATENAME,S1.NAME AS FLASTUPDATENAME,C.FNAME_L2 AS CFCLAZZNAME "
			   		+ "  FROM CT_DS_EXAMSUBSCRIBE T LEFT JOIN CT_DS_CLAZZ C ON T.CFCLAZZID=C.FID LEFT JOIN CT_DS_STUDENT U ON U.FID = T.CFSTUDENTID "
			   		+ " LEFT JOIN T_E_USER S ON T.FCREATORID=S.FID LEFT JOIN T_E_USER S1 ON T.FLASTUPDATEUSERID=S1.FID "
			   		+ " WHERE t.CFFICOMPANYID= '"+fid+"' and to_char(t.cfexamdate,'yyyy-MM-dd') BETWEEN '"+beginTime+"' AND '"+endTime+"'";
		psql +="order by to_char(t.cfexamdate,'yyyy-MM-dd') desc";
		int psize=15; //每页显示数
		int pindex=offset/psize;//+1  页码从0开始	
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap= examOrderService.selectPage(pindex, psql, psize);
		PageBean<ExamOrder> pb=new PageBean<>();
		pb.setPc(pindex); //页码
		Map<String, Object> paramMapCount=getPageBeanCount(psql); //取记录总数
		pb.setTr((int)paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<ExamOrder>)paramMap.get("v_cur")); //每页数据集
		return pb;

	}
	
	
	@SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类 
	 * @throws Exception
	 */
	public PageBean<ExamOrder> getPageBean1(HttpServletRequest request,String fid,String beginTime,String endTime ,ExamOrder  examOrder,boolean isExcel) throws Exception
	{

		int offset;//从第几条数据开始加载
		if(request.getParameter("pager.offset")==null){  
			offset=0;  
		}else{  
			offset=Integer.parseInt(request.getParameter("pager.offset"));  
		}	
		String psql="select  T.FID, to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
				   + "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime," 
				   + "to_char(t.FBIZDATE,'yyyy-MM-DD')  fbizdate,"
				   + "to_char(T.CFEXAMDATE,'YYYY-MM-DD')  cfexamdate,"
				   + "to_char(T.CFNEXTTIME,'YYYY-MM-DD')  cfnexttime,"
				   + "T.FCREATORID, T.FLASTUPDATEUSERID, T.FCONTROLUNITID, "
				   + "T.FNUMBER, T.FHANDLERID, T.FDESCRIPTION, T.FHASEFFECTED, T.FAUDITORID, T.FSOURCEBILLID,  "
				   + "T.FSOURCEFUNCTION, T.FFIVOUCHERED, T.CFFICOMPANYID, T.CFSTUDENTID, T.CFSTUDENTNAME, T.CFMOBILE, "
				   + "T.CFPHONE, T.CFREMARK, T.CFSUBSCRIBERESULT, T.CFBILLTYPE,T.CFCARBYTE,  "
				   + "T.CFARREARAGE, T.CFSQZJCXDH, T.CFVIP, T.CFRECEIVED, T.CFAMOUNT, T.CFFAVORABLE, T.CFCLAZZID, T.CFIDCARDNO,"
				   + " T.CFSEX, T.CFTJR, T.CFCKBK, T.CFJNBJJ, T.CFDAJJ ,U.FNUMBER AS CFSTUDENTNO ,"  	 
				   + "S.NAME AS FCREATENAME,S1.NAME AS FLASTUPDATENAME,C.FNAME_L2 AS CFCLAZZNAME "
			   		+ "  FROM CT_DS_EXAMSUBSCRIBE T LEFT JOIN CT_DS_CLAZZ C ON T.CFCLAZZID=C.FID LEFT JOIN CT_DS_STUDENT U ON U.FID = T.CFSTUDENTID"
			   		+ " LEFT JOIN T_E_USER S ON T.FCREATORID=S.FID LEFT JOIN T_E_USER S1 ON T.FLASTUPDATEUSERID=S1.FID "
			   		+ " WHERE t.CFFICOMPANYID= '"+fid+"' and 1=1 ";
			   		if (examOrder.getCfmobile().equals(null) || examOrder.getCfmobile() == "") {
			
					} else {
						psql += "AND T.CFMOBILE LIKE '%" + examOrder.getCfmobile() + "%'";
					}
					if (examOrder.getCfidcardno().equals(null) || examOrder.getCfidcardno() == "") {
			
					} else {
						psql += "AND T.CFIDCARDNO LIKE '%" + examOrder.getCfidcardno() + "%'";
					}
					if (examOrder.getCfstudentname().equals(null) || examOrder.getCfstudentname() == "") {
			
					} else {
						psql += "AND T.CFSTUDENTNAME LIKE '%" + examOrder.getCfstudentname() + "%'";
					}
					if (examOrder.getCfbilltype() == null || examOrder.getCfbilltype().equals("0")) {

					} else {
						psql +="AND T.cfbilltype = "+examOrder.getCfbilltype();
					}
					if (beginTime.equals(null) || beginTime.equals("")|| endTime.equals(null) || endTime.equals("")) {
					}else {
						psql += "AND TO_CHAR(T.CFEXAMDATE,'yyyy-MM-dd') BETWEEN '" + beginTime + "' AND '" + endTime + "'";
					}
		psql +="order by to_char(t.cfexamdate,'yyyy-MM-dd') desc";
		int psize = 0;
		Map<String, Object> paramMapCount=getPageBeanCount(psql); //取记录总数
		if(isExcel){
			if((int)paramMapCount.get("Pcount") == 0)
				psize = 1;
			else
				psize = (int)paramMapCount.get("Pcount");
		}else {
			psize = 100; //每页显示数
		}
		int pindex=offset/psize;//+1  页码从0开始	
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap= examOrderService.selectPage(pindex, psql, psize);
		PageBean<ExamOrder> pb=new PageBean<>();
		pb.setPc(pindex); //页码
		pb.setTr((int)paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<ExamOrder>)paramMap.get("v_cur")); //每页数据集
		return pb;

	}
	
	/**
	 * 取记录总数 dmz 20160921
	 * @param psql  SQL语句
	 * @return Map
	 * @throws Exception
	 */
	public Map<String, Object> getPageBeanCount(String psql) throws Exception
	{

		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap=examOrderService.selectPageCount(psql);
		return paramMap;		
	}
}
